package com.framework.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.framework.annotation.TableSeg;
import com.framework.exception.SystemException;
import com.framework.plugin.ReflectHelper;
import com.framework.plugin.test.ContextHolder;
import com.framework.plugin.test.DynamicDataSource;
import com.framework.util.datasource.EnvironmentFileInfo;
import com.framework.util.datasource.JDBCSource;


public class ConfigUtils {
	private final Logger logger = Logger.getLogger(ConfigUtils.class);
	/*public static void main(String[] args) {
		new ConfigUtils().initTableField();
	}*/
	/**
	 * 初始化数据库表字段到缓存
	 * @throws IOException 
	 */
	
	public void initTableField() throws IOException {
		// 记录总记录数
		Statement countStmt = null;
		ResultSet rs = null;
		Connection connection = null; // 表示数据库的连接对象
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			/**
			 * @author bys
			 * 读取数据库配置文件信息
			 */
			Properties pro = PropertiesUtils.getjdbcProperties();
			Class.forName(pro.getProperty("jdbc.driverClass")); // 1、使用CLASS
			String url = pro.getProperty("jdbc.url");
			String db = url.substring(url.lastIndexOf("/")+1);
			if(db.indexOf("?")>-1){
				db=db.substring(0, db.indexOf("?"));
			}
			connection = DriverManager.getConnection(url, pro.getProperty("jdbc.username"),
					pro.getProperty("jdbc.password")); // 2、连接数据库
			/**
			 * @author bys
			 * 读取所有的的实体类的表的表名和字段到缓存
			 * 通过自定义的注解@TableSeg拿到表名和id
			 */
			String packageName = "com.framework.entity";
			// List<String> classNames = getClassName(packageName);
			List<String> classNames = ClassUtil.getClassName(packageName, false);
			String tabs = "";
			if (classNames != null) {
				for (String className : classNames) {
					Class<?> clazz = Class.forName(className);
					boolean flag = clazz.isAnnotationPresent(TableSeg.class); // 某个类是不是存在TableSeg注解
					if (flag) {
						TableSeg table = (TableSeg) clazz.getAnnotation(TableSeg.class);
						tabs+="'"+table.tableName()+"',";
						String tableName=table.tableName();
						map.put(tableName, table.id());
					} 
				}
			}
			
			/**
			 * 李品良
			 */
			String packageName2 = "com.framework.entity.view";
			// List<String> classNames = getClassName(packageName);
			List<String> classNames2 = ClassUtil.getClassName(packageName2, false);
			if (classNames != null) {
				for (String className2 : classNames2) {
					Class<?> clazz = Class.forName(className2);
					boolean flag = clazz.isAnnotationPresent(TableSeg.class); // 某个类是不是存在TableSeg注解
					if (flag) {
						TableSeg table = (TableSeg) clazz.getAnnotation(TableSeg.class);
						tabs+="'"+table.tableName()+"',";
						map.put(table.tableName(), table.id());
					} 
				}
			}
			tabs=Common.trimComma(tabs);
			//尽量减少对数据库/IO流操作,一次查询所有表的的字段
			//mysql
			//String sql = "select TABLE_NAME,group_concat(COLUMN_NAME) COLUMN_NAME from information_schema.columns where table_name in ("+tabs+") and table_schema = '"+db+"'  GROUP BY TABLE_NAME" ;
			//sqlserver
			String sql ="";
			if(url.toUpperCase().indexOf("MYSQL")>-1){//mysql
				sql = "select TABLE_NAME,group_concat(COLUMN_NAME) COLUMN_NAME from information_schema.columns where table_name in ("+tabs+") and table_schema = '"+db+"'  GROUP BY TABLE_NAME" ;
			}else if(url.toUpperCase().indexOf("SQLSERVER")>-1){//sqlserver
				sql ="SELECT TABLE_NAME,COLUMN_NAME from( "+
						"SELECT t.name TABLE_NAME, "+
						"(SELECT name+',' FROM syscolumns  "+
						"WHERE id=a.id "+
						 "FOR XML PATH('')) AS COLUMN_NAME "+
						"FROM syscolumns a inner join sysobjects t on a.id = t.id and t.name in ("+tabs+")) a "+
						"GROUP BY a.TABLE_NAME,a.COLUMN_NAME";
			}else if(url.toUpperCase().indexOf("ORACLE")>-1){//ORACLE
				sql ="select TABLE_NAME,WMSYS.WM_CONCAT(column_name)COLUMN_NAME from user_tab_columns"
						  +" where Table_Name in ("+tabs+") GROUP BY table_name"; 
			}else{
				throw new SystemException(" -------------- error ----  ConfigUtils.java  ----  获取数据库类型失败  ------------------------ ");
			}
			
			countStmt = connection.createStatement();
			rs = countStmt.executeQuery(sql);
			
			//TODO
			
			while (rs.next()) {
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("field", Common.trimComma(rs.getString("COLUMN_NAME")));
				String ble =rs.getString("TABLE_NAME");//表名
				m.put("column_key", map.get(ble));//获取表的主键
				EhcacheUtils.put(ble, m);//某表对应的主键和字段放到缓存
			}
		} catch (Exception e) {
			logger.error(" 初始化数据失败,没法加载表字段到缓存 -->> "+e.fillInStackTrace());
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
			}
			try {
				countStmt.close();
			} catch (Exception e) {
			}
		}
	}
	/**
	 * 自定义添加数据库表字段到缓存
	 */
	
	public void addTableField(Connection connection) {
		// 记录总记录数
		Statement countStmt = null;
		ResultSet rs = null;
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			/**
			 * @author bys
			 * 读取所有的的实体类的表的表名和字段到缓存
			 * 通过自定义的注解@TableSeg拿到表名和id
			 */
			String packageName = "com.framework.entity";
			// List<String> classNames = getClassName(packageName);
			List<String> classNames = ClassUtil.getClassName(packageName, false);
			String tabs = "";
			if (classNames != null) {
				for (String className : classNames) {
					Class<?> clazz = Class.forName(className);
					boolean flag = clazz.isAnnotationPresent(TableSeg.class); // 某个类是不是存在TableSeg注解
					if (flag) {
						TableSeg table = (TableSeg) clazz.getAnnotation(TableSeg.class);
						tabs+="'"+table.tableName()+"',";
						map.put(table.tableName(), table.id());
					} 
				}
			}
			tabs=Common.trimComma(tabs);
			//尽量减少对数据库/IO流操作,一次查询所有表的的字段
			//mysql
			//String sql = "select TABLE_NAME,group_concat(COLUMN_NAME) COLUMN_NAME from information_schema.columns where table_name in ("+tabs+") and table_schema = '"+db+"'  GROUP BY TABLE_NAME" ;
			//sqlserver
			String sql ="";
			//找个方法得到，相关的数据，url
			DynamicDataSource dataSource = (DynamicDataSource) SpringFactory.getObject("dynamicDataSource");
			HashMap<String, JDBCSource> hashMap = (HashMap<String, JDBCSource>) ReflectHelper.getValueByFieldName(dataSource,
					"resolvedDataSources");
			JDBCSource jdbcTest=hashMap.get(ContextHolder.getCustomerType());
			
			String url=jdbcTest.getUrl();
			int index=jdbcTest.getUrl().lastIndexOf("/");
			String db=jdbcTest.getUrl().substring(index+1);
			if(url.toUpperCase().indexOf("MYSQL")>-1){//mysql
				sql = "select TABLE_NAME,group_concat(COLUMN_NAME) COLUMN_NAME from information_schema.columns where table_name in ("+tabs+") and table_schema = '"+db+"'  GROUP BY TABLE_NAME" ;
			}else if(url.toUpperCase().indexOf("SQLSERVER")>-1){//sqlserver
				sql ="SELECT TABLE_NAME,COLUMN_NAME from( "+
						"SELECT t.name TABLE_NAME, "+
						"(SELECT name+',' FROM syscolumns  "+
						"WHERE id=a.id "+
						 "FOR XML PATH('')) AS COLUMN_NAME "+
						"FROM syscolumns a inner join sysobjects t on a.id = t.id and t.name in ("+tabs+")) a "+
						"GROUP BY a.TABLE_NAME,a.COLUMN_NAME";
			}else if(url.toUpperCase().indexOf("ORACLE")>-1){//ORACLE
				sql ="select TABLE_NAME,WMSYS.WM_CONCAT(column_name)COLUMN_NAME from user_tab_columns"
						  +" where Table_Name in ("+tabs+") GROUP BY table_name"; 
			}else{
				throw new SystemException(" -------------- error ----  ConfigUtils.java  ----  获取数据库类型失败  ------------------------ ");
			}
			
			countStmt = connection.createStatement();
			rs = countStmt.executeQuery(sql);
			
			//TODO
			
			while (rs.next()) {
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("field", Common.trimComma(rs.getString("COLUMN_NAME")));
				String ble =rs.getString("TABLE_NAME");//表名
				m.put("column_key", map.get(ble));//获取表的主键
				EhcacheUtils.put(ble, m);//某表对应的主键和字段放到缓存
			}
		} catch (Exception e) {
			logger.error(" 初始化数据失败,没法加载表字段到缓存 -->> "+e.fillInStackTrace());
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
			}
			try {
				countStmt.close();
			} catch (Exception e) {
			}
		}
	}
}
